package devicedbgui.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/** Interface to database for acquiring Test Case information */
public class TestCaseDAO {
     /** Stores Connection status */
    private Connection conn;
    /** Constructor used to pass connection status to object
     * @param conn  connection status
     */
    public TestCaseDAO(Connection conn)
    {
        this.conn = conn;
    }
    /** Selects all test cases in database
     * 
     * @throws java.lang.Exception
     */
    public ArrayList<TestCase> selectAll() throws Exception {
        ArrayList<TestCase> result = new ArrayList<TestCase>();

        try { 
            String query = "SELECT * FROM TestCase;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestCase pObj = new TestCase(
                        rs.getInt("idTestCase"),                                               
                        rs.getString("description"),
                        rs.getInt("exe_idexe")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
     /** Inserts a new test case into database
     * 
     * @param pObj
     * @throws java.lang.Exception
     */
    public void insert(TestCase pObj) throws Exception {
       
        try {
            String insert = "INSERT INTO TestCase(" +
                    "description," +                    
                    "exe_idexe)" +
                    "VALUES('" + pObj.description + "','" +                                 
                                 pObj.exe_idexe + "');";

            
            PreparedStatement pstmt = conn.prepareStatement(insert);
            pstmt.executeUpdate();            

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Updates database with the modified test case information 
 * 
 * @param pObj
 * @throws java.lang.Exception
 */
    public void update(TestCase pObj) throws Exception {        
        try {

            String update = "UPDATE TestCase SET " +
                    "description = '" + pObj.description + "'," +                    
                    "exe_idexe = '" + pObj.exe_idexe +
                    "' WHERE idTestCase = '" + pObj.idTestCase + "';";
            
           
            PreparedStatement pstmt = conn.prepareStatement(update);
            pstmt.executeUpdate();
            pstmt.close();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }

 /** Deletes a value from database
 * 
 * @param pId
 * @throws java.lang.Exception
 */
    public void delete(int pId) throws Exception {
       
        try {
            String delete = "DELETE FROM TestCase WHERE idTestCase = '" + pId + "';";
          
            PreparedStatement pstmt = conn.prepareStatement(delete);
            pstmt.executeUpdate();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
    /** Chooses build based on exe id
     * 
     * @param idexe 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestCase> selectByExe(int idexe) throws Exception {
        ArrayList<TestCase> result = new ArrayList<TestCase>();
        try {
            String query = "SELECT * FROM TestCase where exe_idexe = " + idexe +";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestCase pObj = new TestCase(
                        rs.getInt("idTestCase"),                                               
                        rs.getString("description"),
                        rs.getInt("exe_idexe")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    
    /** Gets Device Statistics
     * @param deviceRecord
     * @return ret
     * @throws Exception 
     */
    public ArrayList<DeviceStats> getDeviceStats(String deviceRecord) throws Exception
    {
        ArrayList<DeviceStats> ret = new ArrayList<DeviceStats>();
        try {            
            String query = "select t.description, count(*) as numAppear from TestCase t, " +
                    "exe e, Build b where e.idexe = t.exe_idexe and e.Build_idBuild = b.idBuild " +
                    "and b.Device_record = '" + deviceRecord + "' group by t.description;";
            
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();           
            while (rs.next()) {
                DeviceStats pObj = new DeviceStats();
                String[] tmp = rs.getString("description").split(";");
                if (tmp.length == 2){                     
                    pObj.testCase = tmp[0];
                    if (tmp[1].equals("pass")) {
                        pObj.numberOfPassed = rs.getInt("numAppear");
                    }
                    else {
                        pObj.numberOfFailed = rs.getInt("numAppear");
                    }
                    ret.add(pObj);
                }
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return ret;
    }
    
    /** Gets Device Statistics for all devices
     * @return ret
     * @throws Exception
     */
    public ArrayList<DeviceStats> getDeviceStats() throws Exception
    {
        ArrayList<DeviceStats> ret = new ArrayList<DeviceStats>();
        try {            
            String query = "select t.description, count(*) as numAppear from TestCase t group by t.description;";
            
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();           
            while (rs.next()) {
                DeviceStats pObj = new DeviceStats();
                String[] tmp = rs.getString("description").split(";");
                if (tmp.length == 2){                     
                    pObj.testCase = tmp[0];
                    if (tmp[1].equals("pass"))
                        pObj.numberOfPassed = rs.getInt("numAppear");
                    else
                        pObj.numberOfFailed = rs.getInt("numAppear");
                    ret.add(pObj);
                }
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return ret;
    }
}
